package org.dw.concessionaria.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import org.dw.concessionaria.TO.VendaTO;

public class VendaDAO {

	public int inserir(String notaFiscal, String pagamento, double valor, String rg_vendedor, String rg_cliente, String chassi){
		Connection con=null;
			Statement stmt =null;
			int result = 0;
						
			String comando = "INSERT INTO venda (notafiscal, pagamento, valor, rg_vendedor, rg_cliente, chassi_car) " +
					"VALUES(" + notaFiscal +	",'" + pagamento + "','" + valor + ","
					+ rg_vendedor + "','" + rg_cliente +  "','" + chassi + "')";
			try {
				con=ConnectionDAO.getInstance().getConnection();
				stmt = con.createStatement();
				result = stmt.executeUpdate(comando);
			} catch(Exception e){System.out.println(e.getMessage());}
			finally {
				ConnectionDAO.getInstance().devolveConnection(con);
				try{stmt.close();}catch(Exception ee){};
			}
			return result;
	}
	
	public VendaTO consultaNotaFiscal(String notaFiscal) {
			Connection con=null;
			Statement stmt =null;
			VendaTO venda = null;
			String comando = "SELECT * FROM venda WHERE notafiscal =" + notaFiscal;
			try {
				con=ConnectionDAO.getInstance().getConnection();
				stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(comando);
				if(rs.next()) {
					venda = new VendaTO();
					venda.setChassiNum(rs.getString("chassi_car"));
					venda.setNotafiscal(String.valueOf(rs.getInt("notafiscal")));
					venda.setPagamento(rs.getString("pagamento"));
					venda.setRg_cliente(rs.getString("rg_cliente"));
					venda.setRg_vendedor(rs.getString("rg_vendedor"));
					venda.setValor(rs.getDouble("valor"));
					}
			} catch(Exception e){System.out.println(e.getMessage());}
			finally {
				ConnectionDAO.getInstance().devolveConnection(con);
				try{stmt.close();}catch(Exception ee){};
			}
			return venda;
}
	
	public VendaTO consultaCliente(String rg_cliente) {
		Connection con=null;
		Statement stmt =null;
		VendaTO venda = null;
		String comando = "SELECT * FROM venda WHERE rg_cliente ='" + rg_cliente + "'";
		try {
			con=ConnectionDAO.getInstance().getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(comando);
			if(rs.next()) {
				venda = new VendaTO();
				venda.setChassiNum(rs.getString("chassi_car"));
				venda.setNotafiscal(String.valueOf(rs.getInt("notafiscal")));
				venda.setPagamento(rs.getString("pagamento"));
				venda.setRg_cliente(rs.getString("rg_cliente"));
				venda.setRg_vendedor(rs.getString("rg_vendedor"));
				venda.setValor(rs.getDouble("valor"));
				}
		} catch(Exception e){System.out.println(e.getMessage());}
		finally {
			ConnectionDAO.getInstance().devolveConnection(con);
			try{stmt.close();}catch(Exception ee){};
		}
		return venda;
}	
	
	public VendaTO consultaVendedor(String rg_vendedor) {
		Connection con=null;
		Statement stmt =null;
		VendaTO venda = null;
		String comando = "SELECT * FROM venda WHERE rg_vendedor ='" + rg_vendedor + "'";
		try {
			con=ConnectionDAO.getInstance().getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(comando);
			if(rs.next()) {
				venda = new VendaTO();
				venda.setChassiNum(rs.getString("chassi_car"));
				venda.setNotafiscal(String.valueOf(rs.getInt("notafiscal")));
				venda.setPagamento(rs.getString("pagamento"));
				venda.setRg_cliente(rs.getString("rg_cliente"));
				venda.setRg_vendedor(rs.getString("rg_vendedor"));
				venda.setValor(rs.getDouble("valor"));
				}
		} catch(Exception e){System.out.println(e.getMessage());}
		finally {
			ConnectionDAO.getInstance().devolveConnection(con);
			try{stmt.close();}catch(Exception ee){};
		}
		return venda;
}	
	
	public Collection<VendaTO> consultaModelo() {
		Connection con=null;
		Statement stmt =null;
		Collection<VendaTO> lista = new ArrayList<VendaTO>();
		VendaTO venda = null;
		String comando = "SELECT venda.*, carro.modelo FROM venda, carro WHERE carro.chassi = venda.chassi_car";
		try {
			con=ConnectionDAO.getInstance().getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(comando);
			if(rs.next()) {
				venda = new VendaTO();
				venda.setChassiNum(rs.getString("chassi_car"));
				venda.setNotafiscal(String.valueOf(rs.getInt("notafiscal")));
				venda.setPagamento(rs.getString("pagamento"));
				venda.setRg_cliente(rs.getString("rg_cliente"));
				venda.setRg_vendedor(rs.getString("rg_vendedor"));
				venda.setValor(rs.getDouble("valor"));
				lista.add(venda);
				}
		} catch(Exception e){System.out.println(e.getMessage());}
		finally {
			ConnectionDAO.getInstance().devolveConnection(con);
			try{stmt.close();}catch(Exception ee){};
		}
		return lista;
}	

	public Collection<VendaTO> consultaTodas()
	{
		Connection con=null;
	 	Statement stmt =null;
	 	Collection<VendaTO> lista = new ArrayList<VendaTO>();
	 	VendaTO venda = null;
	 	String comando = "SELECT * FROM venda";
	 try {
		 con=ConnectionDAO.getInstance().getConnection();
	     stmt = con.createStatement();
	     ResultSet rs = stmt.executeQuery(comando);
	     
	     if(rs.next()) {
	    	 venda = new VendaTO();
			 venda.setChassiNum(rs.getString("chassi_car"));
			 venda.setNotafiscal(String.valueOf(rs.getInt("notafiscal")));
			 venda.setPagamento(rs.getString("pagamento"));
			 venda.setRg_cliente(rs.getString("rg_cliente"));
			 venda.setRg_vendedor(rs.getString("rg_vendedor"));
			 venda.setValor(rs.getDouble("valor"));
	    	 lista.add(venda);
	     }
	 } catch(Exception e){System.out.println(e.getMessage());}
	 finally {
		 ConnectionDAO.getInstance().devolveConnection(con);
		 try{stmt.close();}catch(Exception ee){};
	 }
	 return lista;
	}
	
}
